unit UntStocktakingCompare;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, UntDMDB, DBGridEhGrouping, DB, GridsEh, DBGridEh, MemDS, DBAccess,
  MyAccess, ExtCtrls, RzPanel, RzSplit, RzRadGrp, StdCtrls;

type
  TfrmCompare = class(TForm)
    myqryCompare: TMyQuery;
    grdCompare: TDBGridEh;
    dsCompare: TDataSource;
    grddt: TDBGridEh;
    rzspl1: TRzSplitter;
    myqrydt: TMyQuery;
    dsdt: TDataSource;
    rzpnltop: TRzPanel;
    rzrg1: TRzRadioGroup;
    btnexport: TButton;
    rzpnl1: TRzPanel;
    procedure btnexportClick(Sender: TObject);
    procedure grdCompareDrawColumnCell(Sender: TObject; const Rect: TRect; DataCol:
        Integer; Column: TColumnEh; State: TGridDrawState);
    procedure rzrg1Click(Sender: TObject);
  private
    fhdid: Integer;
    fstoreCode :string  ;
    { Private declarations }
  public
    procedure init(hdid: Integer;storeCode :string);
    { Public declarations }
  end;



implementation

uses
  UntDBGridEhToExcel;

{$R *.dfm}

procedure TfrmCompare.btnexportClick(Sender: TObject);
var gtoexl : TDBGridEhToExcel ;
begin
  gtoexl := TDBGridEhToExcel.Create ;
  try
    gtoexl.ExportToExcel(grdCompare);
  finally
    FreeAndNil(gtoexl);
  end;
end;

procedure TfrmCompare.grdCompareDrawColumnCell(Sender: TObject; const Rect:
    TRect; DataCol: Integer; Column: TColumnEh; State: TGridDrawState);
begin
 if myqryCompare.FieldByName('qty').AsInteger<> myqryCompare.FieldByName('totalqty').AsInteger then
  begin
    grdCompare.Canvas.Font.Color := clBlue;
    grdCompare.Canvas.Brush.Color :=clRed;
    grdCompare.Canvas.Pen.Color   :=clRed;
    grdCompare.Canvas.FillRect(Rect);
    grdCompare.DefaultDrawColumnCell(Rect,DataCol,Column,State);;
  end;
end;

procedure TfrmCompare.init(hdid: Integer;storeCode :string);
var strsql :String;
begin
  fhdid := hdid;
  fstoreCode := storeCode;
  myqrydt.Close;
  strsql :='';
  if rzrg1.ItemIndex<3 then
  begin
    strsql := ' select gi.id goodsid,stdt.hdid sthdid,barcode,style,`Description` ,ifnull(sgi.qty,0) qty ,ifnull(totalqty,0)totalqty '
            + ' from goodsinfo gi left join storegoodsinfo sgi on sgi.storecode="'+fstoreCode+'" and sgi.goodsid=gi.id '
            + ' left join (select hdid,goodsid,sum(qty) totalqty  from  stocktakingdt  where hdid = '+inttostr( hdid)+' group by hdid,goodsid)  stdt '
            + ' on  stdt.goodsid=gi.id ' ;
    if rzrg1.ItemIndex=1 then
      strsql :=   strsql + ' where (qty<>0 or totalqty<>0) '   ;
    if rzrg1.ItemIndex=2 then
      strsql := strsql+ ' having  qty<>totalqty  ';
    strsql :=   strsql   + ' order by barcode ';
  end else
  begin
    strsql := ' select gi.id goodsid,stdt.hdid sthdid,barcode,style,`Description` ,ifnull(sgi.qty,0) qty ,ifnull(totalqty,0)totalqty  '
            + ' from  (select hdid,goodsid,sum(qty) totalqty  from  stocktakingdt  where hdid = '+inttostr( hdid)
            + ' group by hdid,goodsid)  stdt   left join  goodsinfo gi  on  stdt.goodsid=gi.id '
            + ' left join storegoodsinfo sgi on sgi.storecode="'+fstoreCode+'" and sgi.goodsid=gi.id ';
    if rzrg1.ItemIndex=4 then
      strsql := strsql+ ' having  qty<>totalqty  ';
    strsql :=   strsql   + ' order by barcode ';
  end;
  with myqryCompare do
  begin
    Close;
    SQL.Clear;
    SQL.Text := strsql;
    Open;
  end;
  myqrydt.Open;
end;

procedure TfrmCompare.rzrg1Click(Sender: TObject);
begin
  init(fhdid,fstoreCode);
end;

end.
